package com.allinpay.util;

import com.allinpay.model.Field;
import com.allinpay.model.Table;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author : wushikai
 * <p>
 * date : 2020-06-19
 *
 *
 */
public class MysqlDataUtils {

    /**
     * jboot.datasource.url=jdbc:mysql://47.111.151.68:3306/clouds_petrol_station?characterEncoding=utf8
     * jboot.datasource.user=root
     * jboot.datasource.password=Py9LvmrTEDxSmyn@
     */
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String DATABASE = "clouds_petrol_station";
    private static final String URL = "jdbc:mysql://47.111.151.68:3306/" + DATABASE + "?useUnicode=true&characterEncoding=utf8&useSSL=false";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "Py9LvmrTEDxSmyn@";

    private static final String SQL = "SELECT * FROM ";// 数据库操作

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("can not load jdbc driver" + e.getMessage());
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            System.err.println("get connection failure" + e.getMessage());
        }
        return connection;
    }

    /**
     * 关闭数据库连接
     *
     * @param connection
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.err.println("close connection failure" + e.getMessage());
            }
        }
    }


    public  static ArrayList<Table> getTablesFormDB ( ){
        Table table=new Table();
        Connection connection=getConnection();
        if (connection==null){
            return null;
        }
        ResultSet resultSet = null;
        try {

            DatabaseMetaData databaseMetaData=connection.getMetaData();
            resultSet = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (resultSet.next()){

                //第一个是db名
                System.out.println("resultSet.getString(1) = " + resultSet.getString(1));
                //第三个是表名
                System.out.println("resultSet.getString(3) = " + resultSet.getString(3));


            }





        }catch (Exception e){
            e.printStackTrace();
        }



        return null;
    }


    public  static Table getTableByTableNameFromDB ( String tableName){


        return null;

    }




    /**
     * 获取数据库下的所有表名
     */
    public static List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        Connection connection = getConnection();
        if (connection==null){
            return null;
        }
        ResultSet resultSet = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            //从元数据中获取到所有的表名
            resultSet = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (resultSet.next()) {
                tableNames.add(resultSet.getString(3));
            }
        } catch (SQLException e) {
            System.err.println("getTableNames failure" + e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                closeConnection(connection);
            } catch (SQLException e) {
                System.err.println("close ResultSet failure" + e);
            }
        }
        return tableNames;
    }

    /**
     * 获取表中所有字段名称
     *
     * @return list<field>  封装好的 字段model 列表
     */
    public static List<Field> getColumnNames(String tableName) {
        List<Field> fields = new ArrayList<>();
        Field field = new Field();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement preparedStatement = null;
        String tableSql =  "SELECT * FROM " + tableName;
//        ResultSet rs = null;
//        rs = pStemt.executeQuery("show full columns from " + tableName);
//        while (rs.next()) {
//            columnComments.add(rs.getString("Comment"));
//        }

        try {
            preparedStatement = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData resultSetMetaData = preparedStatement.getMetaData();
            //结果集元数据列数
            int size = resultSetMetaData.getColumnCount();
            for (int i = 0; i < size; i++) {
                field.setFieldName(resultSetMetaData.getColumnName(i + 1));
                field.setFieldLength(resultSetMetaData.getColumnDisplaySize(i+1));
                field.setFieldType(resultSetMetaData.getColumnTypeName(i+1));
                field.setFieldComment(resultSetMetaData.getColumnLabel(i+1));
                fields.add(field);
            }
        } catch (SQLException e) {
            System.err.println("getColumnNames failure" + e);
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    System.err.println("getColumnNames close pstem and connection failure" + e);
                }
            }
        }
        return fields;
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName
     * @return
     */
    public static List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement preparedStatement = null;
        String tableSql = SQL + tableName;
        try {
            preparedStatement = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData resultSetMetaData = preparedStatement.getMetaData();
            //表列数
            int size = resultSetMetaData.getColumnCount();
            for (int i = 0; i < size; i++) {

                columnTypes.add(resultSetMetaData.getColumnTypeName(i + 1)+ resultSetMetaData.getColumnDisplaySize(i+1));
            }
        } catch (SQLException e) {
            System.err.println("getColumnTypes failure  " + e);
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    System.err.println("getColumnTypes close pstem and connection failure" + e);
                }
            }
        }
        return columnTypes;
    }

    /**
     * 获取表中字段的所有注释
     *
     * @param tableName
     * @return
     */
    public static List<String> getColumnComments(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        List<String> columnComments = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    System.err.println("getColumnComments close ResultSet and connection failure" + e);
                }
            }
        }
        return columnComments;
    }


    public static void main1() throws SQLException, ClassNotFoundException {
        String user = "user";
        String password = "pwd";
        String jdbcDriver = "com.ibm.db2.jcc.DB2Driver";
        String jdbcUrl = "jdbc:db2://localhost:50000/DBNAME";
        Connection conn = null;
        Class.forName(jdbcDriver);
        conn = DriverManager.getConnection(jdbcUrl, user, password);

        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement("select * from t_table where 1=2");
            ResultSetMetaData rsd = pst.executeQuery().getMetaData();
            for(int i = 0; i < rsd.getColumnCount(); i++) {
                System.out.print("java类型："+rsd.getColumnClassName(i + 1));
                System.out.print("  数据库类型:"+rsd.getColumnTypeName(i + 1));
                System.out.print("  字段名称:"+rsd.getColumnName(i + 1));
                System.out.print("  字段长度:"+rsd.getColumnDisplaySize(i + 1));
                System.out.println();
            }
        } catch(SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                pst.close();
                pst = null;
            } catch(SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }


    public static void main(String[] args) {
        List<String> tableNames = getTableNames();
        //System.out.println("tableNames:" + tableNames);
        for (String tableName : tableNames) {
            System.out.println("tableName = " + tableName);
            System.out.println("ColumnNames:" + getColumnNames(tableName));
            System.out.println("ColumnTypes:" + getColumnTypes(tableName));
            System.out.println("ColumnComments:" + getColumnComments(tableName));
        }

       // getTablesFormDB();

    }
}
























